<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <meta name="keywords" content="玖忆;文鹤;博客">
    
    <meta name="author" content="meteor">
    <!-- preconnect -->
    <link rel="preconnect" href="https://fonts.googleapis.com">
    <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
    
    
    <!--- Seo Part-->
    
    <link rel="canonical" href="https://wait-you.github.io/2023/06/06/mybatis批量插入数据的三种方式效率对比/"/>
    <meta name="robots" content="index,follow">
    <meta name="googlebot" content="index,follow">
    <meta name="revisit-after" content="1 days">
    
        <meta name="description" content="# Mybatis 批量插入数据的三种方式效率对比  写项目碰到一个场景，解析从前端传来的 excel 文件，然后逐行添加到数据库中，当当因为是逐行添加，如果采用传统的一行创建一个 sql 的花，效率是比较低的，所以采用批量添加的方式，以下为三种插入数据方式的对比：  # 逐行 insert 首先来看代码： 123456789101112131415161718192021222324252627">
<meta property="og:type" content="article">
<meta property="og:title" content="Mybatis批量插入数据的三种方式效率对比">
<meta property="og:url" content="https://wait-you.github.io/2023/06/06/Mybatis%E6%89%B9%E9%87%8F%E6%8F%92%E5%85%A5%E6%95%B0%E6%8D%AE%E7%9A%84%E4%B8%89%E7%A7%8D%E6%96%B9%E5%BC%8F%E6%95%88%E7%8E%87%E5%AF%B9%E6%AF%94/index.html">
<meta property="og:site_name" content="玖忆">
<meta property="og:description" content="# Mybatis 批量插入数据的三种方式效率对比  写项目碰到一个场景，解析从前端传来的 excel 文件，然后逐行添加到数据库中，当当因为是逐行添加，如果采用传统的一行创建一个 sql 的花，效率是比较低的，所以采用批量添加的方式，以下为三种插入数据方式的对比：  # 逐行 insert 首先来看代码： 123456789101112131415161718192021222324252627">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="http://tuchuang.wenhe9.cn/image-20220818094722514.png">
<meta property="og:image" content="http://tuchuang.wenhe9.cn/image-20220818095032494.png">
<meta property="og:image" content="http://tuchuang.wenhe9.cn/image-20220818094543492.png">
<meta property="article:published_time" content="2023-06-06T01:01:14.000Z">
<meta property="article:modified_time" content="2023-06-05T09:01:50.666Z">
<meta property="article:author" content="meteor">
<meta property="article:tag" content="MyBatis">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="http://tuchuang.wenhe9.cn/image-20220818094722514.png">
    
    
    <!--- Icon Part-->
    <link rel="icon" type="image/png" href="/images/wenhe.png" sizes="192x192">
    <link rel="apple-touch-icon" sizes="180x180" href="/images/wenhe.png">
    <meta name="theme-color" content="#f1404b">
    <link rel="shortcut icon" href="/images/wenhe.png">
    <!--- Page Info-->
    
    <title>
        
            Mybatis批量插入数据的三种方式效率对比 -
        
        玖忆
    </title>
    
<link rel="stylesheet" href="/css/style.css">

    
<link rel="stylesheet" href="/assets/fonts.css">

    <!--- Font Part-->
    
    
    
    

    <!--- Inject Part-->
    
    <script id="hexo-configurations">
    let Global = window.Global || {};
    Global.hexo_config = {"hostname":"wait-you.github.io","root":"/","language":"zh-CN"};
    Global.theme_config = {"articles":{"style":{"font_size":"16px","line_height":1.5,"image_border_radius":"14px","image_alignment":"center","image_caption":false,"link_icon":true},"word_count":{"enable":true,"count":true,"min2read":true},"author_label":{"enable":true,"auto":false,"list":[]},"code_block":{"copy":true,"style":"mac","font":{"enable":false,"family":null,"url":null}},"toc":{"enable":true,"max_depth":3,"number":false,"expand":true,"init_open":true},"copyright":true,"lazyload":true,"recommendation":{"enable":false,"title":"推荐阅读","limit":3,"placeholder":"http://tuchuang.wenhe9.cn/default-bg.jpg","skip_dirs":[]}},"colors":{"primary":"#f1404b","secondary":null},"global":{"fonts":{"chinese":{"enable":false,"family":null,"url":null},"english":{"enable":false,"family":null,"url":null}},"content_max_width":"1000px","sidebar_width":"210px","hover":{"shadow":true,"scale":false},"scroll_progress":{"bar":true,"percentage":true},"busuanzi_counter":{"enable":true,"site_pv":true,"site_uv":true,"post_pv":true},"pjax":true,"open_graph":true,"google_analytics":{"enable":false,"id":null}},"home_banner":{"enable":true,"style":"static","image":{"light":"http://tuchuang.wenhe9.cn/default-bg.jpg","dark":"http://tuchuang.wenhe9.cn/default-bg.jpg"},"title":"玖忆","subtitle":{"text":["我本微末凡尘、可也心向天空"],"hitokoto":{"enable":false,"api":"https://v1.hitokoto.cn"},"typing_speed":100,"backing_speed":80,"starting_delay":500,"backing_delay":1500,"loop":true,"smart_backspace":true},"text_color":{"light":"#fff","dark":"#d1d1b6"},"text_style":{"title_size":"2.8rem","subtitle_size":"1.5rem","line_height":1.2},"custom_font":{"enable":false,"family":null,"url":null},"social_links":{"enable":true,"links":{"github":"https://gitee.com/du-jinliang","instagram":null,"zhihu":null,"twitter":null,"email":"dujinliang9@163.com"}}},"plugins":{"feed":{"enable":false},"aplayer":{"enable":false,"type":"fixed","audios":[{"name":null,"artist":null,"url":null,"cover":null}]},"mermaid":{"enable":false,"version":"9.3.0"}},"version":"2.1.4","navbar":{"auto_hide":true,"color":{"left":"#f78736","right":"#367df7","transparency":35},"links":{"Home":{"path":"/","icon":"fa-regular fa-house"}},"search":{"enable":false,"preload":true}},"page_templates":{"friends_column":2,"tags_style":"blur"},"home":{"sidebar":{"enable":true,"position":"left","first_item":"menu","announcement":null,"links":null},"article_date_format":"auto","categories":{"enable":true,"limit":3},"tags":{"enable":true,"limit":3}}};
    Global.language_ago = {"second":"%s 秒前","minute":"%s 分钟前","hour":"%s 小时前","day":"%s 天前","week":"%s 周前","month":"%s 个月前","year":"%s 年前"};
    Global.data_config = {"masonry":false};
  </script>
    
    <!--- Fontawesome Part-->
    
<link rel="stylesheet" href="/fontawesome/fontawesome.min.css">

    
<link rel="stylesheet" href="/fontawesome/brands.min.css">

    
<link rel="stylesheet" href="/fontawesome/solid.min.css">

    
<link rel="stylesheet" href="/fontawesome/regular.min.css">

    
    
    
    
<meta name="generator" content="Hexo 6.3.0"></head>


<body>
<div class="progress-bar-container">
    
        <span class="scroll-progress-bar"></span>
    

    
        <span class="pjax-progress-bar"></span>
        <span class="pjax-progress-icon">
            <i class="fa-solid fa-circle-notch fa-spin"></i>
        </span>
    
</div>


<main class="page-container">

    

    <div class="main-content-container">

        <div class="main-content-header">
            <header class="navbar-container">
    
    <div class="navbar-content">
        <div class="left">
            
            <a class="logo-title" href="/">
                
                玖忆
                
            </a>
        </div>

        <div class="right">
            <!-- PC -->
            <div class="desktop">
                <ul class="navbar-list">
                    
                        
                            <li class="navbar-item">
                                <!-- Menu -->
                                <a class="" 
                                    href="/"  >
                                    
                                        
                                            <i class="fa-regular fa-house"></i>
                                        
                                        首页
                                    
                                </a>
                                <!-- Submenu -->
                                
                            </li>
                    
                    
                </ul>
            </div>
            <!-- Mobile -->
            <div class="mobile">
                
                <div class="icon-item navbar-bar">
                    <div class="navbar-bar-middle"></div>
                </div>
            </div>
        </div>
    </div>

    <!-- Mobile drawer -->
    <div class="navbar-drawer">
        <ul class="drawer-navbar-list">
            
                
                    <li class="drawer-navbar-item flex-center">
                        <a class="" 
                        href="/"  >
                             
                                
                                    <i class="fa-regular fa-house"></i>
                                
                                首页
                            
                        </a>
                    </li>
                    <!-- Submenu -->
                    
            

        </ul>
    </div>

    <div class="window-mask"></div>

</header>


        </div>

        <div class="main-content-body">

            

            <div class="main-content">

                
                    <div class="fade-in-down-animation">
    <div class="post-page-container">
        <div class="article-content-container">

            
            
                <div class="article-title">
                    <h1 class="article-title-regular">Mybatis批量插入数据的三种方式效率对比</h1>
                </div>
            
                
            

            
                <div class="article-header">
                    <div class="avatar">
                        <img src="/images/wenhe.png">
                    </div>
                    <div class="info">
                        <div class="author">
                            <span class="name">meteor</span>
                            
                                <span class="author-label">Lv3</span>
                            
                        </div>
                        <div class="meta-info">
                            <div class="article-meta-info">
    <span class="article-date article-meta-item">
        <i class="fa-regular fa-pen-fancy"></i>&nbsp;
        <span class="desktop">2023-06-05 17:01:14</span>
        <span class="mobile">2023-06-05 17:01</span>
        <span class="hover-info">创建</span>
    </span>
    
        <span class="article-date article-meta-item">
            <i class="fa-regular fa-wrench"></i>&nbsp;
            <span class="desktop">2023-06-05 01:01:50</span>
            <span class="mobile">2023-06-05 01:01</span>
            <span class="hover-info">更新</span>
        </span>
    

    
        <span class="article-categories article-meta-item">
            <i class="fa-regular fa-folders"></i>&nbsp;
            <ul>
                
                    <li>
                        <a href="/categories/MyBatis/">MyBatis</a>&nbsp;
                    </li>
                
            </ul>
        </span>
    
    
        <span class="article-tags article-meta-item">
            <i class="fa-regular fa-tags"></i>&nbsp;
            <ul>
                
                    <li>
                        <a href="/tags/MyBatis/">MyBatis</a>&nbsp;
                    </li>
                
            </ul>
        </span>
    

    
    
    
    
        <span class="article-pv article-meta-item">
            <i class="fa-regular fa-eye"></i>&nbsp;<span id="busuanzi_value_page_pv"></span>
        </span>
    
</div>

                        </div>
                    </div>
                </div>
            

            <div class="article-content markdown-body">
                <h1 id="mybatis批量插入数据的三种方式效率对比"><a class="markdownIt-Anchor" href="#mybatis批量插入数据的三种方式效率对比">#</a> Mybatis 批量插入数据的三种方式效率对比</h1>
<ul>
<li>写项目碰到一个场景，解析从前端传来的 excel 文件，然后逐行添加到数据库中，当当因为是逐行添加，如果采用传统的一行创建一个 sql 的花，效率是比较低的，所以采用批量添加的方式，以下为三种插入数据方式的对比：</li>
</ul>
<h2 id="逐行insert"><a class="markdownIt-Anchor" href="#逐行insert">#</a> 逐行 insert</h2>
<p>首先来看代码：</p>
<div class="highlight-container" data-rel="Java"><figure class="iseeu highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Component</span></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">SubjectListener</span> <span class="keyword">extends</span> <span class="title class_">AnalysisEventListener</span>&lt;SubjectEeVo&gt; &#123;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span></span><br><span class="line"><span class="comment">     * 锁</span></span><br><span class="line"><span class="comment">     */</span></span><br><span class="line">    <span class="keyword">private</span> <span class="type">ReentrantLock</span> <span class="variable">lock</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">ReentrantLock</span>();</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Resource</span></span><br><span class="line">    <span class="keyword">private</span> SubjectMapper subjectMapper;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Override</span></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">invoke</span><span class="params">(SubjectEeVo subjectEeVo, AnalysisContext analysisContext)</span> &#123;</span><br><span class="line">        lock.lock();</span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            <span class="type">Subject</span> <span class="variable">subject</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">Subject</span>();</span><br><span class="line">            <span class="comment">//SubjectEeVo -&gt; Subject</span></span><br><span class="line">            BeanUtils.copyProperties(subjectEeVo, subject);</span><br><span class="line">            <span class="comment">//添加</span></span><br><span class="line">            subjectMapper.insert(subject);</span><br><span class="line">        &#125; <span class="keyword">finally</span> &#123;</span><br><span class="line">            lock.unlock();</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Override</span></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">doAfterAllAnalysed</span><span class="params">(AnalysisContext analysisContext)</span> &#123;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure></div>
<p>因为 spring 由 ioc 创建的对象默认为单例类，所以会出现线程安全问题，这里采用 <code>reentranlock</code>  来解决这个问题，以下是执行时间：</p>
<p><img  
                     lazyload
                     src="/images/loading.svg"
                     data-src="http://tuchuang.wenhe9.cn/image-20220818094722514.png"
                      alt="image-20220818094722514"
                ></p>
<h2 id="mybatis-的-batch-模式"><a class="markdownIt-Anchor" href="#mybatis-的-batch-模式">#</a> mybatis 的 batch 模式</h2>
<p>mybatis 是由一个 batch, 也就是批量模式，在配置类中添加 <code>sqlSession</code> ，设定为主要的 <code>bean</code></p>
<div class="highlight-container" data-rel="Java"><figure class="iseeu highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Configuration</span></span><br><span class="line"><span class="meta">@MapperScan(&quot;cn.wenhe9.ggkt.*.mapper&quot;)</span></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">MybatisPlusConfig</span> &#123;</span><br><span class="line">    <span class="comment">/**</span></span><br><span class="line"><span class="comment">     * 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题(该属性会在旧插件移除后一同移除)</span></span><br><span class="line"><span class="comment">     */</span></span><br><span class="line">    <span class="meta">@Bean</span></span><br><span class="line">    <span class="keyword">public</span> MybatisPlusInterceptor <span class="title function_">mybatisPlusInterceptor</span><span class="params">()</span> &#123;</span><br><span class="line">        <span class="type">MybatisPlusInterceptor</span> <span class="variable">interceptor</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">MybatisPlusInterceptor</span>();</span><br><span class="line">        interceptor.addInnerInterceptor(<span class="keyword">new</span> <span class="title class_">PaginationInnerInterceptor</span>(DbType.H2));</span><br><span class="line">        <span class="keyword">return</span> interceptor;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Bean</span></span><br><span class="line">    <span class="keyword">public</span> ConfigurationCustomizer <span class="title function_">configurationCustomizer</span><span class="params">()</span> &#123;</span><br><span class="line">        <span class="keyword">return</span> configuration -&gt; configuration.setUseDeprecatedExecutor(<span class="literal">false</span>);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span></span><br><span class="line"><span class="comment">     * 开启批量添加模式</span></span><br><span class="line"><span class="comment">     */</span></span><br><span class="line">    <span class="meta">@Bean</span></span><br><span class="line">    <span class="meta">@Primary</span></span><br><span class="line">    <span class="keyword">public</span> SqlSession <span class="title function_">sqlSession</span><span class="params">(SqlSessionTemplate sqlSessionTemplate)</span> &#123;</span><br><span class="line">        <span class="keyword">return</span> sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, <span class="literal">false</span>);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure></div>
<p>业务代码不需要变更:</p>
<div class="highlight-container" data-rel="Java"><figure class="iseeu highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Component</span></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">SubjectListener</span> <span class="keyword">extends</span> <span class="title class_">AnalysisEventListener</span>&lt;SubjectEeVo&gt; &#123;</span><br><span class="line"></span><br><span class="line">    <span class="comment">/**</span></span><br><span class="line"><span class="comment">     * 锁</span></span><br><span class="line"><span class="comment">     */</span></span><br><span class="line">    <span class="keyword">private</span> <span class="type">ReentrantLock</span> <span class="variable">lock</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">ReentrantLock</span>();</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Resource</span></span><br><span class="line">    <span class="keyword">private</span> SubjectMapper subjectMapper;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Override</span></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">invoke</span><span class="params">(SubjectEeVo subjectEeVo, AnalysisContext analysisContext)</span> &#123;</span><br><span class="line">        lock.lock();</span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            <span class="type">Subject</span> <span class="variable">subject</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">Subject</span>();</span><br><span class="line">            <span class="comment">//SubjectEeVo -&gt; Subject</span></span><br><span class="line">            BeanUtils.copyProperties(subjectEeVo, subject);</span><br><span class="line">            <span class="comment">//添加</span></span><br><span class="line">            subjectMapper.insert(subject);</span><br><span class="line">        &#125; <span class="keyword">finally</span> &#123;</span><br><span class="line">            lock.unlock();</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Override</span></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">doAfterAllAnalysed</span><span class="params">(AnalysisContext analysisContext)</span> &#123;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure></div>
<p>时间略微提升：<img  
                     lazyload
                     src="/images/loading.svg"
                     data-src="http://tuchuang.wenhe9.cn/image-20220818095032494.png"
                      alt="image-20220818095032494"
                ></p>
<h2 id="构建批量插入sql"><a class="markdownIt-Anchor" href="#构建批量插入sql">#</a> 构建批量插入 sql</h2>
<p>这种方式效率是最高的，但也有参数最大数量上限或者 sql 条数的限制或者 sql 语句大小有所限制，这个是由不同的数据库由不同的限制，mysql 语句的长度限制为<strong> 4MB</strong></p>
<p>同样有并发问题，因为需要将每行的数据添加到集合中，再调用一次的 sql 来执行，并且这里是写多读少的情况，所以这里使用 <code>ThreadLocal</code>  来实现</p>
<div class="highlight-container" data-rel="Java"><figure class="iseeu highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Component</span></span><br><span class="line"><span class="keyword">public</span> <span class="keyword">class</span> <span class="title class_">SubjectListener</span> <span class="keyword">extends</span> <span class="title class_">AnalysisEventListener</span>&lt;SubjectEeVo&gt; &#123;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">private</span> ThreadLocal&lt;List&lt;Subject&gt;&gt; subjectTL = <span class="keyword">new</span> <span class="title class_">ThreadLocal</span>&lt;&gt;();</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Resource</span></span><br><span class="line">    <span class="keyword">private</span> SubjectMapper subjectMapper;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Override</span></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">invoke</span><span class="params">(SubjectEeVo subjectEeVo, AnalysisContext analysisContext)</span> &#123;</span><br><span class="line">        List&lt;Subject&gt; subjectList = subjectTL.get();</span><br><span class="line">        <span class="keyword">if</span> (subjectList == <span class="literal">null</span>) &#123;</span><br><span class="line">            subjectList = <span class="keyword">new</span> <span class="title class_">ArrayList</span>&lt;&gt;();</span><br><span class="line">            subjectTL.set(subjectList);</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="type">Subject</span> <span class="variable">subject</span> <span class="operator">=</span> <span class="keyword">new</span> <span class="title class_">Subject</span>();</span><br><span class="line">        <span class="comment">//SubjectEeVo -&gt; Subject</span></span><br><span class="line">        BeanUtils.copyProperties(subjectEeVo, subject);</span><br><span class="line">        <span class="comment">//添加</span></span><br><span class="line">        subjectList.add(subject);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Override</span></span><br><span class="line">    <span class="keyword">public</span> <span class="keyword">void</span> <span class="title function_">doAfterAllAnalysed</span><span class="params">(AnalysisContext analysisContext)</span> &#123;</span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            List&lt;Subject&gt; subjectList = subjectTL.get();</span><br><span class="line">            subjectMapper.insertBatch(subjectList);</span><br><span class="line">        &#125;<span class="keyword">finally</span> &#123;</span><br><span class="line">            subjectTL.remove();</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure></div>
<p>这里对 <code>mapper</code>  和 <code>xml</code>  文件进行修改，新增 <code>insertBatch</code>  批量插入的方法</p>
<div class="highlight-container" data-rel="Java"><figure class="iseeu highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="keyword">interface</span> <span class="title class_">SubjectMapper</span> <span class="keyword">extends</span> <span class="title class_">BaseMapper</span>&lt;Subject&gt; &#123;</span><br><span class="line">    <span class="comment">/**</span></span><br><span class="line"><span class="comment">     * 批量插入数据</span></span><br><span class="line"><span class="comment">     */</span></span><br><span class="line">    <span class="keyword">void</span> <span class="title function_">insertBatch</span><span class="params">(<span class="meta">@Param(&quot;subjectList&quot;)</span> List&lt;Subject&gt; subjectList)</span>;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure></div>
<div class="highlight-container" data-rel="Xml"><figure class="iseeu highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">&lt;?xml version=<span class="string">&quot;1.0&quot;</span> encoding=<span class="string">&quot;UTF-8&quot;</span>?&gt;</span></span><br><span class="line"><span class="meta">&lt;!DOCTYPE <span class="keyword">mapper</span> <span class="keyword">PUBLIC</span> <span class="string">&quot;-//mybatis.org//DTD Mapper 3.0//EN&quot;</span> <span class="string">&quot;http://mybatis.org/dtd/mybatis-3-mapper.dtd&quot;</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">mapper</span> <span class="attr">namespace</span>=<span class="string">&quot;cn.wenhe9.ggkt.vod.mapper.SubjectMapper&quot;</span>&gt;</span></span><br><span class="line"></span><br><span class="line">    <span class="tag">&lt;<span class="name">insert</span> <span class="attr">id</span>=<span class="string">&quot;insertBatch&quot;</span>&gt;</span></span><br><span class="line">        insert into `subject`</span><br><span class="line">            (`id`, `title`, `parent_id`, `sort`)</span><br><span class="line">        values </span><br><span class="line">        <span class="tag">&lt;<span class="name">foreach</span> <span class="attr">collection</span>=<span class="string">&quot;subjectList&quot;</span> <span class="attr">item</span>=<span class="string">&quot;subject&quot;</span> <span class="attr">separator</span>=<span class="string">&quot;,&quot;</span>&gt;</span></span><br><span class="line">            (#&#123;subject.id&#125;, #&#123;subject.title&#125;, #&#123;subject.parentId&#125;, #&#123;subject.sort&#125;)</span><br><span class="line">        <span class="tag">&lt;/<span class="name">foreach</span>&gt;</span></span><br><span class="line">    <span class="tag">&lt;/<span class="name">insert</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">mapper</span>&gt;</span></span><br></pre></td></tr></table></figure></div>
<p>可以看到效率提高了将近一半</p>
<p><img  
                     lazyload
                     src="/images/loading.svg"
                     data-src="http://tuchuang.wenhe9.cn/image-20220818094543492.png"
                      alt="image-20220818094543492"
                ></p>
<h2 id="总结"><a class="markdownIt-Anchor" href="#总结">#</a> 总结</h2>
<p>这里测试的 excel 行数较少，数据可能不够准确，但也足以说明问题</p>
<p><strong>构建批量插入 sql</strong>  &gt; <strong>mybatis 的 batch 模式</strong> &gt; <strong>逐行 insert</strong></p>

            </div>

            
                <div class="post-copyright-info">
                    <div class="article-copyright-info-container">
    <ul>
        <li><strong>标题:</strong> Mybatis批量插入数据的三种方式效率对比</li>
        <li><strong>作者:</strong> meteor</li>
        <li><strong>创建于:</strong> 2023-06-05 17:01:14</li>
        
            <li>
                <strong>更新于:</strong> 2023-06-05 01:01:50
            </li>
        
        <li>
            <strong>链接:</strong> https://gitee.com/du-jinliang/2023/06/06/Mybatis批量插入数据的三种方式效率对比/
        </li>
        <li>
            <strong>版权声明:</strong> 本文章采用 <a class="license" target="_blank" rel="noopener" href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh">CC BY-NC-SA 4.0</a> 进行许可。
        </li>
    </ul>
</div>

                </div>
            

            
                <ul class="post-tags-box">
                    
                        <li class="tag-item">
                            <a href="/tags/MyBatis/">#MyBatis</a>&nbsp;
                        </li>
                    
                </ul>
            

            

            
                <div class="article-nav">
                    
                        <div class="article-prev">
                            <a class="prev"
                            rel="prev"
                            href="/2023/06/06/Node-js/"
                            >
                                <span class="left arrow-icon flex-center">
                                    <i class="fa-solid fa-chevron-left"></i>
                                </span>
                                <span class="title flex-center">
                                    <span class="post-nav-title-item">Node.js</span>
                                    <span class="post-nav-item">上一篇</span>
                                </span>
                            </a>
                        </div>
                    
                    
                        <div class="article-next">
                            <a class="next"
                            rel="next"
                            href="/2023/06/06/Kotlin%E5%8D%8F%E7%A8%8B/"
                            >
                                <span class="title flex-center">
                                    <span class="post-nav-title-item">Kotlin协程</span>
                                    <span class="post-nav-item">下一篇</span>
                                </span>
                                <span class="right arrow-icon flex-center">
                                    <i class="fa-solid fa-chevron-right"></i>
                                </span>
                            </a>
                        </div>
                    
                </div>
            


            
                <div class="comment-container">
                    <div class="comments-container">
    <div id="comment-anchor"></div>
    <div class="comment-area-title">
        <i class="fa-solid fa-comments"></i>&nbsp;评论
    </div>
    

        
            
    <div id="gitalk-container"></div>
    <script data-pjax
            src="//cdn.jsdelivr.net/npm/gitalk/dist/gitalk.min.js"></script>
    <script data-pjax>

        function loadGitalk() {
            let __gitalk__pathname = decodeURI(location.pathname);
            const __gitalk__pathnameLength = __gitalk__pathname.length;
            const __gitalk__pathnameMaxLength = 50;
            if (__gitalk__pathnameLength > __gitalk__pathnameMaxLength) {
                __gitalk__pathname = __gitalk__pathname.substring(0, __gitalk__pathnameMaxLength - 3) + '...';
            }

            try {
                Gitalk && new Gitalk({
                    clientID: '55bad54a77b7e60ad62d',
                    clientSecret: '1031c81500c6be06e338087cb7b713f2d0201b46',
                    repo: 'wait-you.github.io',
                    owner: 'wait-you',
                    admin: ['wait-you'],
                    id: 'comment',
                    language: 'zh-CN'
                }).render('gitalk-container');

            } catch (e) {
                window.Gitalk = null;
            }
        }

        if ('true') {
            const loadGitalkTimeout = setTimeout(() => {
                loadGitalk();
                clearTimeout(loadGitalkTimeout);
            }, 1000);
        } else {
            window.addEventListener('DOMContentLoaded', loadGitalk);
        }
    </script>



        
    
</div>

                </div>
            
        </div>

        
            <div class="toc-content-container">
                <div class="post-toc-wrap">
    <div class="post-toc">
        <div class="toc-title">此页目录</div>
        <div class="page-title">Mybatis批量插入数据的三种方式效率对比</div>
        <ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#mybatis%E6%89%B9%E9%87%8F%E6%8F%92%E5%85%A5%E6%95%B0%E6%8D%AE%E7%9A%84%E4%B8%89%E7%A7%8D%E6%96%B9%E5%BC%8F%E6%95%88%E7%8E%87%E5%AF%B9%E6%AF%94"><span class="nav-text"> Mybatis 批量插入数据的三种方式效率对比</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E9%80%90%E8%A1%8Cinsert"><span class="nav-text"> 逐行 insert</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#mybatis-%E7%9A%84-batch-%E6%A8%A1%E5%BC%8F"><span class="nav-text"> mybatis 的 batch 模式</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%9E%84%E5%BB%BA%E6%89%B9%E9%87%8F%E6%8F%92%E5%85%A5sql"><span class="nav-text"> 构建批量插入 sql</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E6%80%BB%E7%BB%93"><span class="nav-text"> 总结</span></a></li></ol></li></ol>

    </div>
</div>
            </div>
        
    </div>
</div>


                

            </div>
            
            

        </div>

        <div class="main-content-footer">
            <footer class="footer">
    <div class="info-container">
        <div class="copyright-info">
            &copy;
            
              <span>2022</span>
              -
            
            2023&nbsp;&nbsp;<i class="fa-solid fa-heart fa-beat" style="--fa-animation-duration: 0.5s; color: #f54545"></i>&nbsp;&nbsp;<a href="/">meteor</a>
        </div>
        
            <script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
            <div class="website-count info-item">
                
                    <span id="busuanzi_container_site_uv" class="busuanzi_container_site_uv">
                        访问人数&nbsp;<span id="busuanzi_value_site_uv" class="busuanzi_value_site_uv"></span>
                    </span>
                
                
                    <span id="busuanzi_container_site_pv" class="busuanzi_container_site_pv">
                        总访问量&nbsp;<span id="busuanzi_value_site_pv" class="busuanzi_value_site_pv"></span>
                    </span>
                
            </div>
        
        <div class="theme-info info-item">
            <span class="powered-by-container">由 <?xml version="1.0" encoding="utf-8"?><!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 1.1//EN" "http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd"><svg version="1.1" id="圖層_1" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" x="0px" y="0px" width="1rem" height="1rem" viewBox="0 0 512 512" enable-background="new 0 0 512 512" xml:space="preserve"><path fill="#0E83CD" d="M256.4,25.8l-200,115.5L56,371.5l199.6,114.7l200-115.5l0.4-230.2L256.4,25.8z M349,354.6l-18.4,10.7l-18.6-11V275H200v79.6l-18.4,10.7l-18.6-11v-197l18.5-10.6l18.5,10.8V237h112v-79.6l18.5-10.6l18.5,10.8V354.6z"/></svg><a target="_blank" href="https://hexo.io">Hexo</a> 驱动</span>
                <br>
            <span class="theme-version-container">主题&nbsp;<a class="theme-version" target="_blank" href="https://github.com/EvanNotFound/hexo-theme-redefine">Redefine v2.1.4</a>
        </div>
        
            <div class="icp-info info-item"><a target="_blank" rel="nofollow" href="
                
                    beian.miit.gov.cn
                
                ">冀ICP备20010108号</a></div>
        
        
        
            <div id="start_div" style="display:none">
                2022/8/17 11:45:14
            </div>
            <div>
                博客已运行 <span class="odometer" id="runtime_days" ></span> 天 <span class="odometer" id="runtime_hours"></span> 小时 <span class="odometer" id="runtime_minutes"></span> 分钟 <span class="odometer" id="runtime_seconds"></span> 秒
            </div>
        
        
        
            <script async data-pjax>
                try {
                    function odometer_init() {
                    const elements = document.querySelectorAll('.odometer');
                    elements.forEach(el => {
                        new Odometer({
                            el,
                            format: '( ddd).dd',
                            duration: 200
                        });
                    });
                    }
                    odometer_init();
                } catch (error) {}
            </script>
        
        
        
    </div>  
</footer>
        </div>
    </div>

    
        <div class="post-tools">
            <div class="post-tools-container">
    <ul class="article-tools-list">
        <!-- TOC aside toggle -->
        
            <li class="right-bottom-tools page-aside-toggle">
                <i class="fa-regular fa-outdent"></i>
            </li>
        

        <!-- go comment -->
        
            <li class="go-comment">
                <i class="fa-regular fa-comments"></i>
            </li>
        
    </ul>
</div>

        </div>
    

    <div class="right-side-tools-container">
        <div class="side-tools-container">
    <ul class="hidden-tools-list">
        <li class="right-bottom-tools tool-font-adjust-plus flex-center">
            <i class="fa-regular fa-magnifying-glass-plus"></i>
        </li>

        <li class="right-bottom-tools tool-font-adjust-minus flex-center">
            <i class="fa-regular fa-magnifying-glass-minus"></i>
        </li>

        <li class="right-bottom-tools tool-expand-width flex-center">
            <i class="fa-regular fa-expand"></i>
        </li>

        <li class="right-bottom-tools tool-dark-light-toggle flex-center">
            <i class="fa-regular fa-moon"></i>
        </li>

        <!-- rss -->
        

        

        <li class="right-bottom-tools tool-scroll-to-bottom flex-center">
            <i class="fa-regular fa-arrow-down"></i>
        </li>
    </ul>

    <ul class="visible-tools-list">
        <li class="right-bottom-tools toggle-tools-list flex-center">
            <i class="fa-regular fa-cog fa-spin"></i>
        </li>
        
            <li class="right-bottom-tools tool-scroll-to-top flex-center">
                <i class="arrow-up fas fa-arrow-up"></i>
                <span class="percent"></span>
            </li>
        
        
    </ul>
</div>

    </div>

    <div class="image-viewer-container">
    <img src="">
</div>


    


</main>




<script src="/js/utils.js"></script>

<script src="/js/main.js"></script>

<script src="/js/layouts/navbarShrink.js"></script>

<script src="/js/tools/scrollTopBottom.js"></script>

<script src="/js/tools/lightDarkSwitch.js"></script>





    
<script src="/js/tools/codeBlock.js"></script>




    
<script src="/js/layouts/lazyload.js"></script>




    
<script src="/js/tools/runtime.js"></script>

    
<script src="/js/layouts/odometer.min.js"></script>

    
<link rel="stylesheet" href="/assets/odometer-theme-minimal.css">




  
<script src="/js/libs/Typed.min.js"></script>

  
<script src="/js/plugins/typed.js"></script>







<div class="post-scripts pjax">
    
        
<script src="/js/tools/tocToggle.js"></script>

<script src="/js/libs/anime.min.js"></script>

<script src="/js/layouts/toc.js"></script>

<script src="/js/plugins/tabs.js"></script>

    
</div>


    
<script src="/js/libs/pjax.min.js"></script>

<script>
    window.addEventListener('DOMContentLoaded', () => {
        window.pjax = new Pjax({
            selectors: [
                'head title',
                '.page-container',
                '.pjax',
            ],
            history: true,
            debug: false,
            cacheBust: false,
            timeout: 0,
            analytics: false,
            currentUrlFullReload: false,
            scrollRestoration: false,
            // scrollTo: true,
        });

        document.addEventListener('pjax:send', () => {
            Global.utils.pjaxProgressBarStart();
        });

        document.addEventListener('pjax:complete', () => {
            Global.utils.pjaxProgressBarEnd();
            window.pjax.executeScripts(document.querySelectorAll('script[data-pjax], .pjax script'));
            Global.refresh();
        });
    });
</script>




</body>
</html>
